package model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import model.bean.Calendar;
import model.bean.ListCalendar;

public class CalendarDAO extends BaseDAO {

	// load listCalendar order by date
	public ArrayList<ListCalendar> getCalendar() {

		ArrayList<ListCalendar> arrCalendar = new ArrayList<ListCalendar>();
		openDatabase();
		qry = "SELECT * FROM mockdb.calendar where date >= curdate() and (date <= interval 6 day + current_date()) order by date";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();
			ArrayList<Calendar> listCalendar = new ArrayList<Calendar>();
			while (rs.next()) {
				Calendar c = new Calendar();

				c.setIdcalendar(rs.getString("idcalendar"));
				c.setDate(rs.getString("date"));
				c.setStarttime(rs.getString("starttime"));
				c.setEndtime(rs.getString("endtime"));
				c.setTitle(rs.getString("title"));
				c.setContent(rs.getString("content"));
				c.setStatus(rs.getString("status"));
				c.setLocation(rs.getString("location"));

				listCalendar.add(c);
			}
			listCalendar.add(new Calendar("a", "a", "a", "a", "a", "a", "a",
					"a"));
			String s = null;
			ArrayList<Calendar> calendarList = new ArrayList<Calendar>();
			for (Calendar c : listCalendar) {
				if (s == null) {
					s = c.getDate();
				}

				if (s.equalsIgnoreCase(c.getDate())) {
					calendarList.add(c);
				} else {
					ListCalendar l = new ListCalendar();
					l.setDate(s);
					l.setCalendarArr(calendarList);
					arrCalendar.add(l);

					calendarList = new ArrayList<Calendar>();
					s = c.getDate();
					calendarList.add(c);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDatabase();
		}

		return arrCalendar;
	}

	// load listCalendar
	public ArrayList<Calendar> viewCalendar() {

		ArrayList<Calendar> calendar = new ArrayList<Calendar>();
		openDatabase();
		qry = "SELECT * FROM mockdb.calendar order by date";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			while (rs.next()) {
				Calendar c = new Calendar();

				c.setIdcalendar(rs.getString("idcalendar"));
				c.setDate(rs.getString("date"));
				c.setStarttime(rs.getString("starttime"));
				c.setEndtime(rs.getString("endtime"));
				c.setTitle(rs.getString("title"));
				c.setContent(rs.getString("content"));
				c.setStatus(rs.getString("status"));
				c.setLocation(rs.getString("location"));

				calendar.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return calendar;
	}

	public ArrayList<Calendar> viewCalendar(String time) {

		ArrayList<Calendar> calendar = new ArrayList<Calendar>();
		openDatabase();
		qry = "SELECT * FROM mockdb.calendar order by date,starttime";
		if ("today".equalsIgnoreCase(time)) {
			qry = "SELECT * FROM mockdb.calendar where date = curdate() order by date,starttime";
		}
		if ("month".equalsIgnoreCase(time)) {
			qry = "SELECT * FROM mockdb.calendar where monthname(date) = monthname(current_date()) order by date,starttime";
		}
		if ("week".equalsIgnoreCase(time)) {
			qry = "SELECT * FROM mockdb.calendar where week(date) = week(current_date()) order by date,starttime";
		}
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			while (rs.next()) {
				Calendar c = new Calendar();

				c.setIdcalendar(rs.getString("idcalendar"));
				c.setDate(rs.getString("date"));
				c.setStarttime(rs.getString("starttime"));
				c.setEndtime(rs.getString("endtime"));
				c.setTitle(rs.getString("title"));
				c.setContent(rs.getString("content"));
				c.setStatus(rs.getString("status"));
				c.setLocation(rs.getString("location"));

				calendar.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return calendar;
	}

	// void getCalendarID
	public int getCalendarID() {
		openDatabase();
		qry = "SELECT idcalendar FROM mockdb.calendar ORDER BY idcalendar DESC LIMIT 1";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			int id;
			while (rs.next()) {

				id = rs.getInt("idcalendar");
				int calendarID = id + 1;
				return calendarID;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return 1;
	}

	// insert calendar
	public boolean insertCalendar(int id, String date, String starttime,
			String endtime, String title, String content, String status,
			String location) {

		boolean success = false;
		openDatabase();
		qry = "INSERT INTO mockdb.calendar (idcalendar, date, starttime, endtime, title, content, status, location) VALUES ('"
				+ id
				+ "', '"
				+ date
				+ "', '"
				+ starttime
				+ "', '"
				+ endtime
				+ "', N'"
				+ title
				+ "', N'"
				+ content
				+ "', "
				+ status
				+ ",N'"
				+ location + "')";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			success = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return success;

	}
	/**
	 * HIen thi lich quan trong trong tuan
	 * 
	 * @return ArrayList<ListCalendar>
	 * 
	 *         TriLD
	 */
	public ArrayList<Calendar> getCalendarStatus() {

		ArrayList<Calendar> calendar = new ArrayList<Calendar>();
		openDatabase();
		qry = "SELECT * FROM mockdb.calendar where date >= curdate() and (date <= interval 6 day + current_date()) and status = '1' order by date";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();

			while (rs.next()) {
				Calendar c = new Calendar();

				c.setIdcalendar(rs.getString("idcalendar"));
				c.setDate(rs.getString("date"));
				c.setStarttime(rs.getString("starttime"));
				c.setEndtime(rs.getString("endtime"));
				c.setTitle(rs.getString("title"));
				c.setContent(rs.getString("content"));
				c.setStatus(rs.getString("status"));
				c.setLocation(rs.getString("location"));

				calendar.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return calendar;
	}

	/**
	 * Kiem tra thoi gian
	 * 
	 * @return ArrayList<ListCalendar>
	 * 
	 *         TriLD
	 */
	public boolean checkTimeOfDay(String date, String starttime, String endtime) {
		openDatabase();
		qry = "call check_time_of_day2('" + date + "','" + starttime + "','"
				+ endtime + "')";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();
			return !rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeDatabase();
		}
		return false;
	}

	/**
	 * Xóa lịch
	 * 
	 * @return ArrayList<ListCalendar>
	 * 
	 *         TriLD
	 */
	public boolean delete(String id) {

		boolean success = false;
		openDatabase();
		qry = "delete from mockdb.calendar where idcalendar = '" + id + "'";
		try {

			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			success = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return success;
	}

	/**
	 * Hien thi danh sách lich.
	 * 
	 * @return ArrayList<ListCalendar>
	 * 
	 *         TriLD
	 */
	public Calendar getCalendar(String id) {

		Calendar c = new Calendar();
		openDatabase();
		qry = "select * from mockdb.calendar where idcalendar = '" + id + "'";
		try {
			pst = con.prepareStatement(qry);
			rs = pst.executeQuery();
			while (rs.next()) {

				c.setIdcalendar(rs.getString("idcalendar"));
				c.setDate(rs.getString("date"));
				c.setStarttime(rs.getString("starttime"));
				c.setEndtime(rs.getString("endtime"));
				c.setTitle(rs.getString("title"));
				c.setContent(rs.getString("content"));
				c.setStatus(rs.getString("status"));
				c.setLocation(rs.getString("location"));

				return c;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return c;
	}

	/**
	 * Cập nhật lịch công tác.
	 * 
	 * @return ArrayList<ListCalendar>
	 * 
	 *         TriLD
	 */
	public boolean updateCalendar(String idCalendar, String date,
			String starttime, String endtime, String title, String content,
			String status, String location) {
		boolean success = false;
		openDatabase();
		qry = "update mockdb.calendar set date = '" + date + "', starttime = '" + starttime
				+ "', endtime = '" + endtime + "', title = N'" + title
				+ "', content = N'" + content + "', status = " + status
				+ ", location = N'" + location + "' where idcalendar = '"
				+ idCalendar + "'";
		try {
			pst = con.prepareStatement(qry);
			pst.executeUpdate();
			success = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return success;
	}

}
